import json
import mysql.connector
from mysql.connector import Error

class MySQLTool:
    def __init__(self, config_file='config.json'):
        with open(config_file, 'r') as file:
            self.config = json.load(file)

    def get_connection(self, env):
        """ 根据环境(env)获取数据库连接 """
        if env not in self.config:
            raise ValueError(f"Environment '{env}' is not defined in the configuration.")
        
        db_config = self.config[env]
        try:
            connection = mysql.connector.connect(
                host=db_config['host'],
                user=db_config['user'],
                password=db_config['password'],
                database=db_config['database'],
                port=db_config['port']
            )
            if connection.is_connected():
                return connection
        except Error as e:
            print(f"Error while connecting to MySQL: {e}")
            return None

    def executeSql(self, env, sql):
        """ 执行SQL查询并返回结果列表 """
        connection = self.get_connection(env)
        if connection:
            cursor = connection.cursor(dictionary=True)
            try:
                cursor.execute(sql)
                result = cursor.fetchall()
                return result
            except Error as e:
                print(f"Failed to execute query: {e}")
            finally:
                cursor.close()
                connection.close()
        return []
    
    def executeManySql(self, env, sql, data):
        connection = self.get_connection(env)
        if connection:
            cursor = connection.cursor()
            try:
                cursor.executemany(sql, data)
                connection.commit()
            except mysql.connector.Error as e:
                print(f"Failed to execute many queries: {e}")
            finally:
                cursor.close()
                connection.close()

    def update(self, env, sql, data=None):
        rows_affected = -1
        connection = self.get_connection(env)
        if connection:
            cursor = connection.cursor()
            try:
                if data:
                    cursor.execute(sql, data)
                else:
                    cursor.execute(sql)
                connection.commit()
                rows_affected = cursor.rowcount
            except mysql.connector.Error as e:
                print(f"Failed to execute update: {e}")
                connection.rollback()  # 回滚事务以防数据不一致
            finally:
                cursor.close()
                connection.close()
        return rows_affected
    
    def executeSql(self, env, sql, params=None):
        """ 执行SQL查询并返回结果列表 """
        connection = self.get_connection(env)
        if connection:
            cursor = connection.cursor(dictionary=True)
            try:
                cursor.execute(sql, params)  # 传递参数给execute方法
                result = cursor.fetchall()
                return result
            except Error as e:
                print(f"Failed to execute query: {e}")
            finally:
                cursor.close()
                connection.close()
        return []